﻿using UnityEngine;
using System;
using System.Collections;
using System.Collections.Generic;
using org.in2bits.MyXls;
using System.Xml;
using System.IO;
using System.Data;
//using MySQLConntion;
using System.Threading;

public class ExportScores : MonoBehaviour
{
    /// <summary>新建的xls文档</summary>
    XlsDocument xls;
    /// <summary>表格文件的保存地址</summary>
    string OsPath;
    /// <summary>导出线程</summary>
    Thread m_exportThread;

    bool m_isRunning;
    /// <summary>创建Excel信号</summary>
    bool m_createExcelSignal;
    /// <summary>文件是否改变</summary>
    bool m_isChangeLabel;
    /// <summary>可能没有关闭excel 导致导出失败</summary>
    bool m_isExporeError;

    void Start()
    {
        Init();
        m_exportThread.Start();
        Export();
    }

    void Update()
    {
        Point();
    }

    /// <summary>初始化</summary>
    void Init()
    {
        xls = new XlsDocument();
        OsPath ="./score.xls";
        m_exportThread = new Thread(new ParameterizedThreadStart(ThreadExport));
        m_createExcelSignal = false;
        m_isRunning = true;
        m_isChangeLabel = false;
        m_isExporeError = false;
    }

    /// <summary>导出提示</summary>
    void Point()
    {
        if (m_isChangeLabel)
        {
            Debug.Log("导出数据完成");
            m_isChangeLabel = false;
        }

        if (m_isExporeError)
        {
            Debug.Log("导出错误" + '\n' + "请先关闭当前成绩表格再试着导出!");

            m_isExporeError = false;
        }
    }

    /// <summary>导出数据</summary>
    public void Export()
    {
        if (!m_isExporeError)
        {
            Debug.Log("正在导出数据");
        }
        m_createExcelSignal = true;
    }

    /// <summary>导出线程</summary>
    void ThreadExport(object obj)
    {
        while (m_isRunning)
        {
            if (m_createExcelSignal)
            {
                CreatExcel("score.xls");

                m_createExcelSignal = false;
                m_isChangeLabel = true;
            }
        }
    }

    /// <summary>
    /// 创建数据表的信息，包括sheet名
    /// </summary>
     void CreatExcel(string FileName)
     {
         xls = null;
         xls = new XlsDocument();
         xls.FileName = string.Format("{0}", (string)OsPath);//设定文件名和路径

         xls.SummaryInformation.Author = "score"; //填加xls文件作者信息
         xls.SummaryInformation.Subject = "name";//填加文件主题信息 

         AddDataToExcel("sheet1", xls, "TableName");
         AddDataToExcelAll("SheetName", xls);

         try
         {
             File.Delete(OsPath);
             xls.Save(true);
             
         }
         catch (Exception e)
         {
             Debug.Log(e);
             m_isExporeError = true;
         }
     }

    /// <summary>
    /// 查询全部的成绩，并为数据表添加数据
    /// 参数1为sheet名
    /// </summary>
    void AddDataToExcel(string sheetName, XlsDocument xls,string tableName)
    {
        Worksheet sheetLand = xls.Workbook.Worksheets.Add(sheetName);
        SetColumnsAttribute(sheetLand);
        Cells cells = sheetLand.Cells;//Cells实例是sheet页中单元格（cell）集合 

        //向第一行第一列写入名字
        cells.Add(1, 1, "名字", SetCellAttribute());
        //向第一行第二列写入学号
        cells.Add(1, 2, "学号", SetCellAttribute());
        cells.Add(1, 3, "成绩", SetCellAttribute());
    }

    /// <summary>
    /// 分配好数据表的格局，并添加数据
    /// </summary>
    void AddDataToExcelAll(string sheetName, XlsDocument xls)
    {
        
        Worksheet sheetAll = xls.Workbook.Worksheets.Add(sheetName);
        SetColumnsAttribute(sheetAll);
        Cells cells = sheetAll.Cells;//Cells实例是sheet页中单元格（cell）集合


        //写入数据
         cells.Add(1, 1, "学号", SetCellAttribute());
         cells.Add(1, 2, "姓名", SetCellAttribute());
         cells.Add(1, 3, "加油模式", SetCellAttribute());
         cells.Add(1, 4, "加油工种", SetCellAttribute());
         cells.Add(1, 5, "成绩", SetCellAttribute());
    }

    void AddData()
    {

    }

    /// <summary>
    /// 设置单元格的属性
    /// </summary>
    XF SetCellAttribute()
    {
        // 列标题样式
        XF columnTitleXF = xls.NewXF(); // 为xls生成一个XF实例，XF是单元格格式对象
        columnTitleXF.HorizontalAlignment = HorizontalAlignments.Centered; // 设定文字居中
        columnTitleXF.VerticalAlignment = VerticalAlignments.Centered; // 垂直居中
        columnTitleXF.UseBorder = true; // 使用边框 

        columnTitleXF.TopLineStyle = 1; // 上边框样式
        columnTitleXF.TopLineColor = Colors.Black; // 上边框颜色
        columnTitleXF.BottomLineStyle = 1; // 下边框样式
        columnTitleXF.BottomLineColor = Colors.Black; // 下边框颜色
        columnTitleXF.LeftLineStyle = 1; // 左边框样式
        columnTitleXF.LeftLineColor = Colors.Black; // 左边框颜色
        columnTitleXF.RightLineStyle = 1;
        columnTitleXF.RightLineColor = Colors.Black;

        columnTitleXF.Pattern = 1; // 单元格填充风格。如果设定为0，则是纯色填充(无色)，1代表没有间隙的实色 
        columnTitleXF.PatternBackgroundColor = Colors.Red; // 填充的底色 
        columnTitleXF.PatternColor = Colors.Default2F; // 填充背景色

        return columnTitleXF;
    }

    /// <summary>
    /// 设置列的属性
    /// </summary>
    void SetColumnsAttribute(Worksheet sheet)
    {
        // 学号列设置
        ColumnInfo col0 = new ColumnInfo(xls, sheet); // 列对象 
        col0.ColumnIndexStart = 0; // 起始列为第1列，索引从0开始
        col0.ColumnIndexEnd = 10; // 终止列为第1列，索引从0开始
        col0.Width = 16 * 256; // 列的宽度计量单位为 1/256 字符宽
        sheet.AddColumnInfo(col0); // 把格式附加到sheet页上
    }

    void OnApplicationQuit()
    {
        m_isRunning = false;
        m_exportThread.Abort();
    }
}
